from cfg import GlobalCfg
from core.lib import MySQL
from core.po import Page
from core.util import StrUtil, TimeUtil

dbCfg = GlobalCfg.Values['db']

"""数据库操作对象"""
class Dao:
    cnx = None
    cfg = None
    table = None
    idKey = None
    listKey = None

    """构造函数"""
    def __init__(self, database,  table):
        if dbCfg.get(database) is None:
            raise "数据库配置不存在"
        cfg = dbCfg[database]
        self.table = table
        self.idKey = self.table + "_id"
        self.listKey = self.table + "_list"
        self.cnx = MySQL.get_connector(user=cfg['user'], password=cfg['password'], host=cfg['host'], database=database)

    """获取列表数据"""
    def find(self, fields: list, where: str, params: list, order_by: str=None, page: Page.Page=None):
        retData = {self.listKey:[], 'total':-1}
        if fields is None:
            sql = " SELECT * "
        else:
            sql = " SELECT " + StrUtil.join(fields, ', ')

        sql += " FROM " + self.table + " WHERE " + where

        #排序处理
        if order_by is not None:
            sql += " ORDER BY {}".format(order_by)

        if page.get_total() > -1:
            sqlCount = " SELECT COUNT(*) FROM (" + sql + ") AS " + self.table + "_" + str(TimeUtil.get_timestamp())
            retTotal = MySQL.query(self.cnx, sqlCount, tuple(params))
            retData['total'] = retTotal[0][0]
        #分页处理
        if page is None:
            sql += " LIMIT 1"
        else:
            sql += " LIMIT " + str(page.get_start()) + ", " + str(page.get_size())

        dataRows = MySQL.query(self.cnx, sql, tuple(params))
        retData[self.listKey] = dataRows
        return retData

    # 纯AND的条件简单获取#
    def get(self, fields: list, params: dict, page: Page.Page=None, order_by: str=None, ):
        whVals = params.values()
        whCond = list()
        for fld in params.keys():
            whCond.append(fld + "=?")

        wh = StrUtil.join(whCond, ' AND')
        return self.find(fields, wh, list(whVals), page=page, order_by=order_by)

    """返回"""
    def count(self, where: str, params: list):
        sql = " SELECT COUNT(*) "
        sql += " FROM " + self.table + " WHERE " + where

        qdata = MySQL.query(self.cnx, sql, tuple(params))
        return qdata[0][0]

    """删除记录"""
    def delete(self, where, params: list):
        sql = " DELETE FROM " + self.table + " WHERE " + where
        deleteNum = MySQL.execute(self.cnx, sql, tuple(params))
        return deleteNum

    """插入记录"""
    def insert(self, data: dict):
        colNames = data.keys()
        vals = data.values()
        emptyTags = ['%s'] * len(colNames)

        sql = "INSERT INTO " + self.table
        sql += "(" + StrUtil.join(colNames, ', ') + ") VALUES(" + StrUtil.join(emptyTags, ", ") + ")"
        try:
            MySQL.execute(self.cnx, sql, tuple(vals))
        except Exception as e:
            raise e

    """数据修改"""
    def update(self, where: str, params: list, updata: dict):
        upKeys = updata.keys()
        upFields = [x+"=%s" for x in upKeys]

        sql = " UPDATE " + self.table + " SET " + StrUtil.join(upFields, ",")
        sql += " WHERE " + where

        upparams = list(updata.values())
        upparams += params
        upNum = MySQL.execute(self.cnx, sql, tuple(upparams))
        return upNum

    """
    直接的sql语句执行
    除了简单的增删改查，其他统计类的都是用这方法
    """
    def sql(self, sql: str, params: list):
        return MySQL.query(self.cnx, sql, tuple(params))

    """创建表"""
    def createTable(self, sql):
        MySQL.execute_dml_sql(self.cnx, sql)
        pass

    """删除表"""
    def dropTable(self, tabName: str):
        MySQL.execute_dml_sql(self.cnx, tabName)
        pass

